""" 数据库操作类 """
# from plugin.logger import InnerLogger
from dbutils.pooled_db import PooledDB
# from plugin.exception import MyException
import sqlite3

# logger = InnerLogger.log


class DB:
    """ 数据库操作类，父类 """
    __pool: dict = {}  # link: pool 每个数据库连接参数有自己的线程池

    def __init__(self, module, mincached=5, maxcached=10, maxconnections=100, blocking=True, maxusage=100, **conninfo) -> None:
        """
        @param:
            module: 数据库连接包，如pymysql
            host: 数据库ip
            port: 数据库端口
            db: 库名
            user: 用户名
            passwd: 密码
            charset: 字符编码
            mincached: 连接池中空闲连接的初始数量
            maxcached: 连接池中空闲连接的最大数量
            maxconnections: 创建连接池的最大数量
            blocking: 超过最大连接数量时候的表现，为True等待连接数量下降，为false直接报错处理
            maxusage: 单个连接的最大重复使用次数
        """
        link = ''  # 连接参数拼接的字符串，用作线程池的key
        for key, value in conninfo.items():  # dict不能作为key，只好把他们连接起来
            assert value, '数据库连接初始化失败，缺少必要字段{}'.format(key)
            link += str(value)
        if not self.__class__.__pool.__contains__(link):
            try:
                pool = PooledDB(module,
                                mincached,
                                maxcached,
                                maxshared=10,
                                maxconnections=maxconnections,
                                blocking=blocking,
                                maxusage=maxusage,
                                setsession=None,
                                reset=True,
                                **conninfo)
                # cursorclass=module.cursors.DictCursor)

                self.__class__.__pool[link] = pool
            except Exception as e:
                # logger.error(MyException.ERROR_DATABASE_CONN)
                # print(e)
                print(e)
        self._link = link
        self._conn = None
        self._cursor = None
        self.__get_conn()

    def __get_conn(self):
        self._conn = self.__pool[self._link].connection()
        self._cursor = self._conn.cursor()

    def close(self):
        try:
            self._cursor.close()
            self._conn.close()
        except Exception as e:
            print(e)

    def execute(self, sql, args=None):
        try:
            effect_rows = self.__execute(sql, param=args)
            self._conn.commit()
        except Exception as e:
            self._conn.rollback()
            print(e)
            raise e
        return effect_rows

    def __execute(self, sql, param=()):
        try:
            effect_rows = self._cursor.execute(sql, param)
            return effect_rows
        except Exception as e:
            print(e)
            return None

    def select(self, sql, param=()):
        """
        查询多个结果
        :param sql: qsl语句
        :param param: sql参数
        :return: 结果数量和查询结果集
        """
        try:
            count = self.__execute(sql, param)
            result = self._cursor.fetchall()
            return result
        except Exception as e:
            print(e)
            return None

    def execute_without_commit(self, sql, param=()):
        count = self.__execute(sql, param)
        return count

    def begin(self):
        """开启事务"""
        self._conn.autocommit(0)

    def end(self, option='commit'):
        """结束事务"""
        if option == 'commit':
            self._conn.autocommit()
        else:
            self._conn.rollback()


class MysqlClient(DB):
    """ mysql操作类 """
    def __init__(self,
                 host=None,
                 port=None,
                 db=None,
                 user=None,
                 passwd=None,
                 charset='utf8',
                 mincached=5,
                 maxcached=10,
                 maxconnections=100,
                 blocking=True,
                 maxusage=100) -> None:
        try:
            import pymysql
        except Exception as e:
            print(e)
            return
        conn_info = {
            'host': host,
            'port': port,
            'db': db,
            'user': user,
            'passwd': passwd,
            'charset': charset,
            'cursorclass': pymysql.cursors.DictCursor
        }
        super().__init__(pymysql,
                         charset=charset,
                         mincached=mincached,
                         maxcached=maxcached,
                         maxconnections=maxconnections,
                         blocking=blocking,
                         maxusage=maxusage,
                         **conn_info)


class MSsqlClient(DB):
    """ sqlserver操作类 """
    def __init__(self,
                 host=None,
                 port=None,
                 database=None,
                 user=None,
                 password=None,
                 charset='utf8',
                 mincached=5,
                 maxcached=10,
                 maxconnections=100,
                 blocking=True,
                 maxusage=100) -> None:
        try:
            import pymssql
        except Exception as e:
            print(e)
            return
        conn_info = {'host': host, 'port': port, 'database': database, 'user': user, 'password': password, 'charset': charset}
        super().__init__(pymssql,
                         mincached=mincached,
                         maxcached=maxcached,
                         maxconnections=maxconnections,
                         blocking=blocking,
                         maxusage=maxusage,
                         **conn_info)

    def select_to_dict(self, sql, param=()) -> list:
        """ 将查询的结果和title组成list(dict) """
        result = []
        datas = super().select(sql, param)
        if datas is not None:
            title = [item[0] for item in self._cursor.description]
            for data in datas:
                result.append(dict(zip(title, data)))
        return result

    def insert_object_list(self, objects: list, tablename, columns: list = None):
        """
        将对象列表插入数据库，需要提供数据和表名，将自动切割数据并拼接语句插入
        @param:
            @datas: 对象类型的数据
            @tablename: 数据库名称
            @columns: 提供的列明，没有提供的话，默认时datas的字段名
        """
        if not objects or len(objects) == 0:
            return True
        columns = list(objects[0].__dict__.keys()) if not columns else columns  # 获取列名
        ins_sql = "insert into {} ({}) values ".format(tablename, ','.join(columns))  # 拼接操作语句
        value_sql = "(" + ("'{}'," * len(columns))[:-1] + ")"  # 拼接单个值语句
        for cut_datas in [objects[i:i + 300] for i in range(0, len(objects), 300)]:
            sql = ins_sql + ','.join([value_sql.format(*data.__dict__.values()) for data in cut_datas])  # 拼接成插入300条的长语句
            self.execute(sql)
        return True


class RedisClient(DB):
    """ redis操作类,get获取，set插入 """

    __pool: dict = {}  # link: pool 每个数据库连接参数有自己的线程池

    def __init__(self, host=None, port=None, password=None, db=0, maxconnections=100):
        try:
            import redis
        except Exception as e:
            print(e)
            print(e)
            return
        link = host + port + str(db)  # 连接参数拼接的字符串，用作线程池的key
        if not self.__class__.__pool.__contains__(link):
            try:
                pool = redis.ConnectionPool(host=host, port=port, db=db, password=password, max_connections=maxconnections, decode_responses=False)
                self.__class__.__pool[link] = pool
            except Exception as e:
                print(e)
        self._link = link
        self._conn = redis.Redis(connection_pool=self.__pool[link])

    def __execute(self, datas: dict, mode: int):
        try:
            if mode == 0:  # 更新&&插入
                self._conn.mset(datas)
            else:
                for key, value in datas:
                    if mode == 1:  # 插入，如果已存在，不更新
                        self._conn.set(key, value, nx=True)
                    elif mode == 2:  # 更新，如果不存在，不处理
                        self._conn.set(key, value, xx=True)
            return True
        except Exception as e:
            print(e)
            return False

    def update(self, datas: dict):
        """ 仅更新已有的 """
        return self.__execute(datas, 2)

    def add(self, datas: dict):
        """ 仅新增没有的 """
        return self.__execute(datas, 1)

    def set(self, datas: dict):
        """ 没有的进行新增，已有的进行更新 """
        return self.__execute(datas, 0)

    def get(self, keys: list):
        """
        查询多个结果
        """
        result: list = self._conn.mget(keys)
        return result

    def get_to_dict(self, keys: list):
        """ 将查询结果和key组合成字典 """
        result = self._conn.mget(keys)
        return dict(zip(keys, result))


class Sqlite():
    """ sqlite操作类 """
    database = 'data.db'
    __conn = None

    def __init__(self, dbname=None) -> None:
        """ 默认读写项目文件下面的data.db """
        dbname = dbname or Sqlite.database
        if not self.__class__.__conn:
            self.__class__.__conn = sqlite3.connect(dbname, check_same_thread=False)

    def __select(self, sql, param=()):
        """ 内部查询 """
        cursor = self.__conn.cursor()
        cursor.execute(sql, param)
        result = cursor.fetchall()
        column = cursor.description
        cursor.close()
        return result, column

    def select(self, sql, param=()):
        """ 查询 """
        return self.__select(sql, param)[0]

    def execute(self, sql, param=()):
        """ 执行 """
        cursor = self.__conn.cursor()
        cursor.execute(sql, param)
        self.__conn.commit()
        cursor.close()

    def select_to_dict(self, sql, param=()):
        """ 查询结果转换成字典 """
        result, column = self.__select(sql, param)
        return [dict(zip([c[0] for c in column], r)) for r in result]

    def insert_object_list(self, objects: list, tablename=None, columns: list = None):
        """
        将对象列表插入数据库，需要提供数据和表名，将自动切割数据并拼接语句插入
        @param:
            @datas: 对象类型的数据
            @tablename: 数据库名称
            @columns: 提供的列名，没有提供的话，默认时datas的字段名
        """
        if not objects or len(objects) == 0:
            return True
        tablename = tablename or objects[0].__class__.__name__
        columns = list(objects[0].__dict__.keys()) if not columns else columns  # 获取列名
        ins_sql = "insert into {} ({}) values ".format(tablename, ','.join(columns))  # 拼接操作语句
        value_sql = "(" + ("'{}'," * len(columns))[:-1] + ")"  # 拼接单个值语句
        for cut_datas in [objects[i:i + 300] for i in range(0, len(objects), 300)]:
            sql = ins_sql + ','.join([value_sql.format(*data.__dict__.values()) for data in cut_datas])  # 拼接成插入300条的长语句
            self.execute(sql)
        return True

    def update_object_list(self, objects: list, tablename=None, columns: list = None, where_columns: list = None):
        """
        将对象列表更新数据库，需要提供数据和表名，将自动切割数据并拼接语句插入
        @param:
            @datas: 对象类型的数据
            @tablename: 数据库名称,默认为对象类型的名称
            @columns: 提供的列名，没有提供的话，默认时datas的字段名
            @where_columns: 条件列名，没有提供的话，默认为id
        """
        if not objects or len(objects) == 0:
            return True
        tablename = tablename or objects[0].__class__.__name__
        columns = columns or list(objects[0].__dict__.keys())  # 获取列名
        where_columns = where_columns or ['id']  # 获取条件列名
        upd_columns = [col for col in columns if col not in where_columns]  # 去除条件列名

        upd_set_sql = ','.join(["{} = '{{{}}}'".format(column, column) for column in upd_columns])
        upd_where_sql = ' and '.join(["{} = '{{{}}}'".format(column, column) for column in where_columns])
        #  👇 update tablename set column1={column1},column2={column2} where id={id}
        upd_sql = "update {} set {} where {}".format(tablename, upd_set_sql, upd_where_sql)  # 拼接操作语句
        print(upd_sql)
        for cut_datas in [objects[i:i + 300] for i in range(0, len(objects), 300)]:
            sql = ';'.join([upd_sql.format(**data.__dict__) for data in cut_datas])  # 拼接成插入300条的长语句
            print(sql)
            self.execute(sql)
        return True
